﻿using Npgsql;
using Ortodoctor.Funcionario.modelo;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Services;

namespace Ortodoctor.modelo
{
    public class Servico
    {
        public bool verificaPrimeiroAcessoDia()
        {
            bool fechou = false;
            //string connectionstring = ("Server=186.202.152.24;Port=5432;User Id=ortodoctor1;Password=DycbqDfHLI;Database=ortodoctor1;MinPoolSize=5;CommandTimeout=60;");
            string connectionstring = ("Server=localhost;Port=5432;User Id=postgres;Password=postgres;Database=ortodoctor;MinPoolSize=5;CommandTimeout=60;");
            NpgsqlConnection conn;
            //conexao con = new conexao();

            conn = new NpgsqlConnection(connectionstring);
            conn.Open();
            DataTable dt = new DataTable();
            //con.open();'
            String sql = "select valor from configuracoes where parametro = 'ultima_data'";
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
            da.Fill(dt);
            conn.Close();

            string hoje = DateTime.Now.ToString("dd/MM/yyyy");
            
            string data = dt == null || dt.Rows.Count == 0 ? hoje : dt.Rows[0]["valor"].ToString();
            if (hoje.Equals(data))
            {
                fechou = true;
            }

            if (!fechou)
            {
                conn.Open();
                DataTable dt1 = new DataTable();
                sql = @"with parcela as(select *
                            from parcelas
                            where data_vencimento > (now() - interval '1 MONTH') AND DATA_VENCIMENTO < NOW() )

                            select id_cliente, id_procedimento_cliente, id_parcela from procedimento_cliente
                            inner join parcela using(id_procedimento_cliente) where 
                            id_cliente not in (select id_cliente from comparecimento where data > (now() - interval '1 MONTH') AND DATA_VENCIMENTO < NOW())";
                da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt1);
                conn.Close();

                conn.Open();
                sql = "update configuracoes set valor = '" + hoje + "' where parametro = 'ultima_data' ";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.ExecuteNonQuery();
                conn.Close();
                
                DateTime dtime = Convert.ToDateTime(data);                

                dtime = dtime.AddDays(-1);

                conn.Open();
                dt = new DataTable();
                sql = "select * from parcelas where data_vencimento='" + dtime.ToString("MM/dd/yyyy") + "' and status ='EM DIA'";
                da = new NpgsqlDataAdapter(sql, conn);
                try
                {
                    da.Fill(dt);
                }
                catch 
                {
                    //System.Windows.Forms.MessageBox.Show("data errada = "+dtime.ToString());
                }
                conn.Close();

                conn.Open();
                sql = "update parcelas set status = 'ATRASADA' where data_vencimento = '" + dtime.ToString("MM/dd/yyyy") + "' and status ='EM DIA'";
                
                com = new NpgsqlCommand(sql, conn);
                com.ExecuteNonQuery();
                conn.Close();

                int cont = 0;
                int dia = 0;

                foreach (DataRow dr in dt.Rows)
                {
                    dt1.DefaultView.RowFilter = "[id_parcela]='" + dr["id_parcela"] + "'";
                    if(dt1.DefaultView.ToTable().Rows.Count > 0)
                    {
                        conn.Open();
                        sql = "update parcelas set status = 'TRANSFERIDA' where id_parcela=" + dt1.DefaultView.ToTable().Rows[0]["id_parcela"];
                        com = new NpgsqlCommand(sql, conn);
                        com.ExecuteNonQuery();
                        conn.Close();

                        conn.Open();
                        DataTable temp = new DataTable();
                        sql = "select max(data_vencimento) as data_maxima, id_procedimento_cliente from parcelas where id_procedimento_cliente = " + dr["id_procedimento_cliente"].ToString() + " group by id_procedimento_cliente";
                        da = new NpgsqlDataAdapter(sql, conn);
                        da.Fill(temp);
                        conn.Close();

                        DateTime datetime = Convert.ToDateTime(temp.Rows[0]["data_maxima"]);

                        dia = datetime.Day;
                        int mes = datetime.Month;
                        int ano = datetime.Year;
                        DateTime dateParcela = new DateTime();
                        for (int j = 0; j <= 5; ++j)
                        {
                            try
                            {
                                if (cont == 1)
                                    cont++;
                                else
                                    if (cont == 2)
                                    {
                                        mes--;
                                        ++cont;
                                    }
                                dateParcela = Convert.ToDateTime((dia) + "/" + (++mes) + "/" + (ano));
                                if (cont == 3)
                                    cont = 0;
                                break;
                            }
                            catch
                            {
                                if (mes > 12)
                                {
                                    mes = 0;
                                    ano++;
                                }
                                else
                                {
                                    dia = 1;
                                    ++cont;
                                }
                            }
                        }
                        modeloParcelas parcelas = new modeloParcelas();
                        parcelas.insert(new Componentes.Parcelas(-1,
                                                                Convert.ToDouble(dr["valor"]),
                                                                dateParcela,
                                                                Convert.ToInt32(dr["id_procedimento_cliente"]),
                                                                "EM DIA"));

                    }
                    conn.Close();

                }
            }
        
            modeloComparecimento model = new modeloComparecimento();
            return model.getMaxDate();
        }

        public static string ImageToBase64(Image image, System.Drawing.Imaging.ImageFormat format)
        {
            using (MemoryStream ms = new MemoryStream())
            {
                // Convert Image to byte[]
                image.Save(ms, format);
                byte[] imageBytes = ms.ToArray();

                // Convert byte[] to Base64 String
                string base64String = Convert.ToBase64String(imageBytes);
                return base64String;
            }
        }

        public static Image Base64ToImage(string base64String)
        {
            // Convert Base64 String to byte[]
            byte[] imageBytes = Convert.FromBase64String(base64String);
            MemoryStream ms = new MemoryStream(imageBytes, 0,
              imageBytes.Length);

            // Convert byte[] to Image
            ms.Write(imageBytes, 0, imageBytes.Length);
            Image image = Image.FromStream(ms, true);
            return image;
        }
    }
}